package com.app.service;

import com.app.domain.dto.ExcelMapHandleReqDto;
import com.app.domain.dto.ExcelMapHandleRule;
import com.app.mapper.BaseDataTestMapper;
import com.app.utils.BaseDataExcelImportListener;
import lombok.AllArgsConstructor;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * easy excel test service
 *
 * @author ch
 * @date 2023/1/12 16:40
 */
@Service
@AllArgsConstructor
public class EasyExcelTestService {

    private final BaseDataTestMapper baseDataTestMapper;

    public void uploadHandle(ExcelMapHandleReqDto param) {
        String tableName = param.getTableName();
        List<ExcelMapHandleRule> rules = param.getRules();
        // todo 从缓存中获取excel上传的数据
        List<HashMap<String, Object>> data = (List<HashMap<String, Object>>) BaseDataExcelImportListener.CACHE.get("data");

        List<String> saveSql = buildSql(tableName, rules, data);

        // sql执行
        for (String sql : saveSql) {
            baseDataTestMapper.executeInsertSql(sql);
        }
    }

    /**
     * 大量数据分批次执行，获取分批次执行的sql集合
     *
     * @param tableName 表名
     * @param rules     字段信息-excel表头映射关系
     * @param data      excel导入的数据
     * @return List<String> sql集合
     */
    private List<String> buildSql(String tableName, List<ExcelMapHandleRule> rules, List<HashMap<String, Object>> data) {

        List<String> sqlList = new ArrayList<>();

        int offset = 0;
        int round = data.size() / 500;
        for (int i = 0; i < round; i++) {
            int off = offset;
            offset = offset + 500;
            List<HashMap<String, Object>> subList = data.subList(off, offset);
            sqlList.add(buildOneBatchSql(tableName, rules, subList));
        }
        List<HashMap<String, Object>> subList = data.subList(offset, data.size());
        if (!CollectionUtils.isEmpty(subList)) {
            sqlList.add(buildOneBatchSql(tableName, rules, subList));
        }
        return sqlList;
    }

    /**
     * 封装批量插入sql（oracle语法）
     * * insert into table(c1,c2,c3) select c1,c2,c3 from dual union all select select c1,c2,c3 from dual
     *
     * @param tableName 表名
     * @param rules     字段信息-excel表头映射关系
     * @param data      excel导入的数据
     * @return String sql语句
     */
    private String buildOneBatchSql(String tableName, List<ExcelMapHandleRule> rules, List<HashMap<String, Object>> data) {
        StringBuilder sb = new StringBuilder("insert into ").append(tableName).append("(");

        List<String> columns = new ArrayList<>();
        for (ExcelMapHandleRule rule : rules) {
            columns.add(rule.getColumnName());
        }
        sb.append(String.join(",", columns));
        sb.append(") ");
        List<String> values = new ArrayList<>();
        for (HashMap<String, Object> row : data) {
            List<String> rowValue = new ArrayList<>();
            for (ExcelMapHandleRule rule : rules) {
                rowValue.add(buildSqlValue(rule, row));
            }
            values.add("select " + String.join(",", rowValue) + " from dual");
        }
        sb.append(String.join(" union all ", values));
        return sb.toString();
    }

    /**
     * 根据映射规则封装字段数据
     *
     * @param rule 映射规则
     * @param row  数据
     * @return String 数据
     */
    private String buildSqlValue(ExcelMapHandleRule rule, HashMap<String, Object> row) {
        String columnType = rule.getColumnType();
        String excelHeadName = rule.getExcelHeadName();
        String columnName = rule.getColumnName();

        Object value = row.get(StringUtils.isEmpty(excelHeadName) ? columnName : excelHeadName);
        if (null == value) {
            return "null";
        }
        switch (columnType) {
            case "DATE":
                // todo sysdate是临时处理，需根据格式去修改
                return "sysdate";
            default:
                return "'" + value + "'";
        }
    }

}
